DROP TABLE IF EXISTS jms_dm.dm_city_prescription_reach_rate_dt ;
CREATE EXTERNAL TABLE jms_dm.dm_city_prescription_reach_rate_dt(
  date_time date COMMENT '日期',
  island bigint COMMENT '订单来源',
  districtprescription bigint COMMENT '区县时效',
  is_pres_sign bigint COMMENT '时效签收',
  is_real_sign bigint COMMENT '实际签收',
  start_city_id bigint COMMENT '始发城市id',
  sign_city_id bigint COMMENT '签收城市id',
  start_subordinate_agent_code string COMMENT '始发代理区code',
  start_subordinate_agent_name string COMMENT '始发代理区name',
  start_regional_id string COMMENT '始发大区id',
  start_regional_desc string COMMENT '始发大区name',
  start_provider_id string COMMENT '始发省份id',
  start_provider_desc string COMMENT '始发省份name',
  start_city_desc string COMMENT '始发城市name',
  sign_subordinate_agent_code string COMMENT '签收代理区code',
  sign_subordinate_agent_name string COMMENT '签收代理区name',
  sign_regional_id string COMMENT '签收大区id',
  sign_regional_desc string COMMENT '签收大区name',
  sign_provider_id string COMMENT '签收省份id',
  sign_provider_desc string COMMENT '签收省份name',
  sign_city_desc string COMMENT '签收城市name',
  economic_circle_type string COMMENT '经济圈名称',
  circle_type bigint COMMENT '是否经济圈',
  platform_effect_type bigint COMMENT '是否平台时效',
  platform_effect bigint COMMENT '平台时效',
  real_reach_sum bigint COMMENT '实际达成票数',
  real_no_reach_sum bigint COMMENT '实际未达成票数',
  real_total bigint COMMENT '实际总票量',
  real_reach_rate bigint COMMENT '实际达成率',
  pres_reach_sum bigint COMMENT '时效达成票数',
  pres_no_reach_sum bigint COMMENT '时效未达成票数',
  pres_total bigint COMMENT '时效总票量',
  pres_reach_rate bigint COMMENT '时效达成率',
  real_platform_reach_sum bigint COMMENT '平台实际达成票数',
  real_platform_no_reach_sum bigint COMMENT '平台实际未达成票数',
  real_platform_total bigint COMMENT '平台实际总票量',
  real_platform_reach_rate bigint COMMENT '平台实际达成率',
  pres_platform_reach_sum bigint COMMENT '平台时效达成票数',
  pres_platform_no_reach_sum bigint COMMENT '平台时效未达成票数',
  pres_platform_total bigint COMMENT '平台时效总票量',
  pres_platform_reach_rate bigint COMMENT '平台时效达成率')
COMMENT '区县时效3期城市-城市汇总表'
PARTITIONED BY (
  dt string COMMENT '时间分区(yyyyMMdd)')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_city_prescription_reach_rate_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );

MSCK REPAIR TABLE jms_dm.dm_city_prescription_reach_rate_dt;



DROP TABLE IF EXISTS jms_dm.dm_city_prescription_reach_rate_plan_dt ;
CREATE EXTERNAL TABLE jms_dm.dm_city_prescription_reach_rate_plan_dt(
  date_time date COMMENT '日期',
  island bigint COMMENT '订单来源',
  districtprescription bigint COMMENT '区县时效',
  is_pres_sign bigint COMMENT '时效签收',
  is_real_sign bigint COMMENT '实际签收',
  start_city_id bigint COMMENT '始发城市id',
  sign_city_id bigint COMMENT '签收城市id',
  start_subordinate_agent_code string COMMENT '始发代理区code',
  start_subordinate_agent_name string COMMENT '始发代理区name',
  start_regional_id string COMMENT '始发大区id',
  start_regional_desc string COMMENT '始发大区name',
  start_provider_id string COMMENT '始发省份id',
  start_provider_desc string COMMENT '始发省份name',
  start_city_desc string COMMENT '始发城市name',
  sign_subordinate_agent_code string COMMENT '签收代理区code',
  sign_subordinate_agent_name string COMMENT '签收代理区name',
  sign_regional_id string COMMENT '签收大区id',
  sign_regional_desc string COMMENT '签收大区name',
  sign_provider_id string COMMENT '签收省份id',
  sign_provider_desc string COMMENT '签收省份name',
  sign_city_desc string COMMENT '签收城市name',
  economic_circle_type string COMMENT '经济圈名称',
  circle_type bigint COMMENT '是否经济圈',
  platform_effect_type bigint COMMENT '是否平台时效',
  platform_effect bigint COMMENT '平台时效',
  real_reach_sum bigint COMMENT '实际达成票数',
  real_no_reach_sum bigint COMMENT '实际未达成票数',
  real_total bigint COMMENT '实际总票量',
  real_reach_rate bigint COMMENT '实际达成率',
  pres_reach_sum bigint COMMENT '时效达成票数',
  pres_no_reach_sum bigint COMMENT '时效未达成票数',
  pres_total bigint COMMENT '时效总票量',
  pres_reach_rate bigint COMMENT '时效达成率',
  real_platform_reach_sum bigint COMMENT '平台实际达成票数',
  real_platform_no_reach_sum bigint COMMENT '平台实际未达成票数',
  real_platform_total bigint COMMENT '平台实际总票量',
  real_platform_reach_rate bigint COMMENT '平台实际达成率',
  pres_platform_reach_sum bigint COMMENT '平台时效达成票数',
  pres_platform_no_reach_sum bigint COMMENT '平台时效未达成票数',
  pres_platform_total bigint COMMENT '平台时效总票量',
  pres_platform_reach_rate bigint COMMENT '平台时效达成率')
COMMENT '区县时效3期城市-城市汇总表'
PARTITIONED BY (
  dt string COMMENT '时间分区(yyyyMMdd)')
    STORED AS PARQUET
    LOCATION '/dw/hive/jms_dm.db/external/dm_city_prescription_reach_rate_plan_dt'
    TBLPROPERTIES (
        'discover.partitions' = 'false',
        'parquet.column.index.access' = 'true'
        );

MSCK REPAIR TABLE jms_dm.dm_city_prescription_reach_rate_plan_dt;